--usage example:  sqlplus @apps/apps/juldbgqa @file_name application_id
SET serveroutput on size 1000000
SET feedback off
  spool Report_AppId&1..htm
DECLARE

   TYPE rule_id_table IS TABLE OF ame_rules.rule_id%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE rule_desc_table IS TABLE OF ame_rules.description%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE action_id_table IS TABLE OF ame_actions.action_id%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE action_desc_table IS TABLE OF ame_actions.description%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE action_type_name_table IS TABLE OF ame_action_types.NAME%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE action_parameter_table IS TABLE OF ame_actions.parameter%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE action_type_id_table IS TABLE OF ame_action_types.action_type_id%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE condn_id_table IS TABLE OF ame_conditions.condition_id%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE condn_type_table IS TABLE OF VARCHAR2 (25) 
   INDEX BY BINARY_INTEGER;

   TYPE attr_name_table IS TABLE OF ame_attributes.NAME%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE attr_type_table IS TABLE OF ame_attributes.attribute_type%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE attr_value_table IS TABLE OF ame_attribute_usages.query_string%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE attr_is_line_item_table IS TABLE OF ame_attributes.line_item%TYPE 
   INDEX BY BINARY_INTEGER;

   TYPE rule_type_table IS TABLE OF varchar2(30) 
   INDEX BY BINARY_INTEGER;

   TYPE rule_type_num_table is table of number
   index by binary_integer;

   TYPE is_static_table IS TABLE OF ame_attribute_usages.is_static%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE approval_group_id_table IS TABLE OF ame_approval_groups.approval_group_id%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE approval_group_name_table IS TABLE OF ame_approval_groups.name%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE approval_group_par_name_table IS TABLE OF ame_approval_group_items.parameter_name%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE approval_group_par_table IS TABLE OF ame_approval_group_items.parameter%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE config_vars_name_table IS TABLE OF ame_config_vars.variable_name%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE config_vars_value_table IS TABLE OF ame_config_vars.variable_value%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE rule_usages_start_date IS TABLE OF ame_rule_usages.start_date%TYPE
   INDEX BY BINARY_INTEGER;

   TYPE rule_usages_lifespan IS TABLE OF varchar2(100)
   INDEX BY BINARY_INTEGER;

   TYPE rule_usages_priority IS TABLE OF ame_rule_usages.priority%TYPE
   INDEX BY BINARY_INTEGER;


   CURSOR c_rule (app_id NUMBER) IS
      SELECT   amerl.rule_id, amerl.description,
               DECODE(
                     amerl.rule_type,
                     1,'List Creation Rules',
                     2,'List Creation Exception Rules',
                     3,'List Modification Rules',
                     4,'Substitution Rules',
                     5,'Pre List Rules',
                     6,'Post List Rules',
                     'Undefined Rules'
                  ),amerl.rule_type
      FROM  ame_rules amerl
      WHERE amerl.rule_id  IN (SELECT rule_id
                               FROM ame_rule_usages
                               WHERE item_id = app_id
                                     AND 
                                     SYSDATE <= NVL((end_date-(1/86400)),SYSDATE)
                              )
            and sysdate  <= nvl((amerl.end_date-(1/86400)),sysdate)
      order by amerl.rule_type;


   CURSOR c_rule_usages (appid NUMBER,ruleid NUMBER) IS
      SELECT   ameru.start_date,
               DECODE(ameru.end_date,null,start_date || ' Onward',start_date||' to '|| (ameru.end_date -(1/86400))),
               ameru.priority
      FROM  ame_rule_usages   ameru
      WHERE ameru.item_id = appid
            AND ameru.rule_id = ruleid
            AND sysdate <= NVL(ameru.end_date-(1/86400),SYSDATE);


   CURSOR c_action (ruleid number) is
      select   ameact.action_id, act.name, ameact.description
      from     ame_actions ameact, ame_action_types act
      where    act.action_type_id = ameact.action_type_id
               and ameact.action_id in (select  action_id
                                        from ame_rules
                                        where rule_id = ruleid
                                              and sysdate <=nvl (end_date-(1/86400),sysdate)
                                        )
               and sysdate <= nvl (act.end_date-(1/86400),sysdate)
               and sysdate <= nvl (ameact.end_date-(1/86400),sysdate);


   CURSOR c_action_types is
      select   act.action_type_id, act.name
      from     ame_action_types act
      where    act.action_type_id <> -1
               and sysdate between act.start_date and nvl(act.end_date-(1/86400),sysdate);


   CURSOR c_action_type_attr_details (act_typ_id number) is
      select   ameattr.name, ameattr.attribute_type
      from     ame_attributes ameattr
      where    ameattr.attribute_id in (select  attribute_id
                                        from    ame_mandatory_attributes
                                        where   action_type_id = act_typ_id
                                                and sysdate between  start_date and 
                                                    nvl(end_date-(1/86400),sysdate)
                                        )
               and sysdate between  ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
      ORDER BY ameattr.NAME;


   CURSOR c_action_type_action_details (act_typ_id NUMBER) IS
      SELECT   ameact.action_id, ameact.description,
               ameact.parameter
      FROM     ame_actions ameact
      WHERE    ameact.action_type_id = act_typ_id
               AND SYSDATE BETWEEN ameact.start_date AND NVL(ameact.end_date-(1/86400),SYSDATE)
      ORDER BY ameact.action_id;


   CURSOR c_condition (ruleid NUMBER) IS
      SELECT   amecon.condition_id,
               DECODE(amecon.condition_type,'auth',' ','pre','Exception : ','post', 'List Modification : ')
      FROM     ame_conditions amecon
      WHERE    amecon.condition_id IN (SELECT condition_id
                                       FROM ame_condition_usages
                                       WHERE rule_id = ruleid
                                             AND SYSDATE <=NVL (end_date-(1/86400),SYSDATE)
                                       )
               AND SYSDATE <=NVL (amecon.end_date-(1/86400),SYSDATE);


   CURSOR c_attr_details_mandatory_item (app_id NUMBER) IS
      SELECT   ameattr.NAME attribute_name, ameattr.attribute_type,ameatusg.query_string,
               ameatusg.is_static
      FROM     ame_attributes ameattr, ame_attribute_usages ameatusg
      WHERE    ameattr.attribute_id IN (SELECT   attribute_id
                                        FROM     ame_mandatory_attributes amemattr
                                        WHERE    action_type_id = -1
                                                 AND SYSDATE BETWEEN amemattr.start_date AND 
                                                     NVL (amemattr.end_date-(1/86400),SYSDATE)
                                       )
               AND ameattr.attribute_id = ameatusg.attribute_id
               AND ameatusg.application_id = app_id
               AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
               AND SYSDATE BETWEEN ameatusg.start_date AND NVL(ameatusg.end_date-(1/86400),SYSDATE)
      ORDER BY ameattr.line_item, ameattr.NAME;


   CURSOR c_attr_details_header_item (app_id NUMBER) IS
      SELECT   ameattr.NAME attribute_name, ameattr.attribute_type, ameatusg.query_string,
               ameatusg.is_static
      FROM     ame_attributes ameattr, ame_attribute_usages ameatusg
      WHERE    ameattr.attribute_id NOT IN (SELECT attribute_id
                                            FROM ame_mandatory_attributes amemattr
                                            WHERE action_type_id = -1
                                                  AND SYSDATE BETWEEN amemattr.start_date AND 
                                                      NVL(amemattr.end_date-(1/86400),SYSDATE)
                                           )
              AND ameattr.attribute_id = ameatusg.attribute_id
              AND ameatusg.application_id = app_id
              AND (ameattr.line_item IS NULL OR ameattr.line_item = 'N')
              AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
              AND SYSDATE BETWEEN ameatusg.start_date AND NVL((ameatusg.end_date-(1/86400)),SYSDATE)
      ORDER BY ameattr.line_item, ameattr.NAME;


   CURSOR c_attr_details_line_item (app_id NUMBER) IS
      SELECT ameattr.NAME attribute_name, ameattr.attribute_type,ameatusg.query_string,
             ameatusg.is_static
      FROM ame_attributes ameattr, ame_attribute_usages ameatusg
      WHERE ameattr.attribute_id NOT IN (SELECT attribute_id
										           FROM ame_mandatory_attributes amemattr
										           WHERE action_type_id = -1
												           AND SYSDATE BETWEEN amemattr.start_date AND 
															  NVL(amemattr.end_date-(1/86400),SYSDATE)
													 )
	         AND ameattr.attribute_id = ameatusg.attribute_id
            AND ameatusg.application_id = app_id
            AND ameattr.line_item = 'Y'
            AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
            AND SYSDATE BETWEEN ameatusg.start_date AND NVL(ameatusg.end_date-(1/86400),SYSDATE) 
      ORDER BY ameattr.line_item, ameattr.NAME;


  CURSOR c_approval_groups IS
   SELECT  ameapg.approval_group_id,ameapg.name,ameapg.query_string,
           ameapg.is_static
   FROM    ame_approval_groups ameapg
   WHERE   SYSDATE BETWEEN ameapg.start_date AND 
	        NVL(ameapg.end_date-(1/86400),SYSDATE)
   ORDER BY ameapg.is_static,ameapg.approval_group_id;


   CURSOR c_approval_group_items (approval_group NUMBER) IS
      SELECT ameapgi.approval_group_item_id, ameapgi.parameter_name,
             ameapgi.parameter, ameapgi.order_number
      FROM   ame_approval_group_items ameapgi
      WHERE  ameapgi.approval_group_id = approval_group
             AND SYSDATE BETWEEN ameapgi.start_date AND 
				     NVL(ameapgi.end_date-(1/86400),SYSDATE) 
      ORDER BY ameapgi.order_number;


   CURSOR c_config_vars (app_id NUMBER) IS
      SELECT amecv.variable_name,amecv.variable_value
      FROM   ame_config_vars amecv
      WHERE  amecv.application_id = app_id
             AND sysdate BETWEEN amecv.start_date AND 
				     NVL(amecv.end_date-(1/86400),sysdate);


   CURSOR c_config_default_vars IS
      SELECT amecv.variable_name,amecv.variable_value
      FROM ame_config_vars amecv
      WHERE amecv.application_id is null
            AND sysdate BETWEEN amecv.start_date AND 
                NVL(amecv.end_date-(1/86400),sysdate);


   tx_typ_id             VARCHAR2 (50);
   txname                VARCHAR2 (240);
   prodname              VARCHAR2 (240);
   appid                 NUMBER;
   ruleid                ame_rules.rule_id%TYPE   := 0;
   s_no                  NUMBER;
   color_code            NUMBER                   := 1;
   color_tag             VARCHAR2 (7);
   line_item_query       VARCHAR2 (4000);
   attribute_typ         VARCHAR2 (1);
   is_static_details     is_static_table;
   attr_name_details     attr_name_table;
   attr_type_details     attr_type_table;
   attr_values           attr_value_table;


  PROCEDURE printline (line VARCHAR2) IS
    is_exist   BOOLEAN          := TRUE;
    printstr   VARCHAR2 (255);
    idx        NUMBER           := 1;
  BEGIN
    WHILE is_exist LOOP
      printstr := SUBSTR (line, idx, 255);
      IF (LENGTH(printstr) IS NULL OR LENGTH (printstr) = 0) THEN
        is_exist := FALSE;
      ELSE
        idx := idx + 255;
        DBMS_OUTPUT.put_line (printstr);
      END IF;
    END LOOP;
  END printline;




FUNCTION get_priority (appid NUMBER,ruletype NUMBER) RETURN boolean is
   v_appid          number;
   v_ruletype       number;
   v_variable_value varchar2(200);
   v_left_position  number;
   v_right_position number;
   v_position_value varchar2(20);
begin
   v_appid:=appid;
   v_ruletype := ruletype;
   begin
       select x.variable_value into v_variable_value
       from   ame_config_vars x
       where  x.application_id=appid and
              x.variable_name = 'rulePriorityModes' and
              sysdate between x.start_date and nvl(x.end_date-(1/86400),sysdate);     
   exception
       when no_data_found then
               select x.variable_value into v_variable_value
               from   ame_config_vars x
               where  x.application_id is null and
                      x.variable_name = 'rulePriorityModes' and
                      sysdate between x.start_date and nvl(x.end_date-(1/86400),sysdate);     
   end;

   v_variable_value := ':'||v_variable_value||':';
   v_left_position := instr(v_variable_value,':',1,v_ruletype);
   v_right_position := instr(v_variable_value,':',1,v_ruletype+1);
   v_position_value := substr(v_variable_value,v_left_position+1,v_right_position-v_left_position-1);

   if v_position_value = 'disabled' then
      return false;
   else
      return true;
   end if;
end get_priority;


procedure print_attributes (attribute_title varchar2,attr_name_details attr_name_table,attr_type_details attr_type_table,
                            is_static_details is_static_table,attr_values attr_value_table) is
begin
  IF attr_name_details.COUNT > 0 THEN
    printline ('<a name="'||attribute_title||'"></a><br>');        
    printline ('<a href="#top">Top</a>');
    printline ('<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD colspan="4"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">'||attribute_title||' Attributes'||' :</FONT></B></TD></TR>');
    printline ('<BR><TR bgcolor="#00F2FA"><TD>Attribute Name</TD><TD>Attribute Type</TD><TD>Static</TD><TD>Attribute Value</TD></TR>');
    FOR attr_idx IN 1 .. attr_name_details.COUNT LOOP
      printline ('<TR><TD width=20%>' || attr_name_details (attr_idx) || '</TD><TD>' || attr_type_details (attr_idx));
      printline ('</TD><TD>');
      printline(is_static_details (attr_idx));
      printline ('</TD><TD>');
      printline (NVL (TRIM (attr_values (attr_idx)), '.'));
      printline ('</TD></TR>');
    END LOOP;
    printline ('</TABLE>');
  END IF;
end print_attributes;


procedure print_rules_actions is
  rule_details          rule_id_table;
  rule_desc_details     rule_desc_table;
  rule_type_details     rule_type_table; 
  rule_type_num_details rule_type_num_table;
  action_details        action_id_table;
  action_desc_details   action_desc_table;
  action_type_details   action_type_name_table;
  condn_details         condn_id_table;
  condn_type_details    condn_type_table;
  rule_usages_start_det rule_usages_start_date;
  rule_usages_lifespan_det rule_usages_lifespan;
  rule_usages_priority_det rule_usages_priority;
  v_status              boolean;
  rule_type_state       varchar2(30);
begin
  printline ('<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD align=center><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Rule Details</FONT></B></TD></TR></TABLE>');
  OPEN c_rule (appid);
  FETCH c_rule BULK COLLECT INTO rule_details, rule_desc_details,rule_type_details,rule_type_num_details;
  CLOSE c_rule;

  rule_type_state:='initialized';

  FOR idx IN 1 .. rule_details.COUNT LOOP
    if rule_type_state <> rule_type_details (idx) then
        printline ('<a name="Rule_Details'||rule_type_num_details (idx) || '"></a><br>');        
   rule_type_state := rule_type_details (idx);
        printline ('<a href="#top">Top</a>');
   printline (
      '<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD align=left><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">' || rule_type_state ||'</FONT></B></TD></TR></TABLE>'
   );
    end if;
    printline ('<TABLE width="100%" BORDER ="1" cellspacing=0>');
    printline ('<TR BGCOLOR="#00F2FA"><TD width=27%><B>Rule ID   :' || rule_details (idx) || '</B></TD>');
    printline ('<TD><B>Rule Description : ' || rule_desc_details (idx) || '</B></TD>');
    printline ('</TR><TABLE width="100%" BORDER ="1" cellspacing=0>' || '<TR><TD width="12%" > </TD>');
    printline ('<TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Conditions     :</B></TD></TR>');

    OPEN c_condition (rule_details (idx));
    FETCH c_condition BULK COLLECT INTO condn_details, condn_type_details;
    CLOSE c_condition;

    FOR con_idx IN 1 .. condn_details.COUNT LOOP
      printline ('<TR><TD width="12%"> </TD><TD>');
      printline (condn_details (con_idx) || '</TD><TD>');
      printline (condn_type_details (con_idx) || ame_condition_pkg.getdescription (condn_details (con_idx)));
      printline ('</TD></TR>');
    END LOOP;

    printline ('<TR><TD width="12%" > </TD><TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Actions    :</B></TD>');
    printline('</TR>');

    OPEN c_action (rule_details (idx));
    FETCH c_action BULK COLLECT INTO action_details, action_type_details,action_desc_details;
    CLOSE c_action;

    FOR act_idx IN 1 .. action_details.COUNT LOOP
      printline ('<TR><TD width="12%"> </TD><TD>');
      printline (action_details (act_idx) || '</TD><TD>');
      printline (action_type_details (act_idx) || ' : ' || action_desc_details (act_idx) || '</TD></TR>');
    END LOOP;
--rule usages
    OPEN c_rule_usages (appid,rule_details (idx));
    FETCH c_rule_usages BULK COLLECT INTO rule_usages_start_det, rule_usages_lifespan_det,
             rule_usages_priority_det;
    CLOSE c_rule_usages;

    printline ('<TR><TD width="12%" > </TD><TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Usages    :</B></TD>');
    printline('<TR ><TD width="12%"></TD><TD bgcolor="#cccc66">Lifespan</TD><TD bgcolor="#cccc66">Priority</TD></TR>');
    FOR usage_idx IN 1 .. rule_usages_start_det.COUNT LOOP
      printline ('<TR><TD width="12%"> </TD><TD>');
      if rule_usages_start_det (usage_idx) > sysdate then
          printline('**' || rule_usages_lifespan_det (usage_idx) ||'</TD><TD>');
      else
          printline (rule_usages_lifespan_det (usage_idx) || '</TD><TD>');
      end if;
      --print priority if not disabled
      v_status := get_priority(appId,rule_type_num_details(idx));
      if v_status = true then
         printline ( rule_usages_priority_det (usage_idx)|| '</TD></TR>');
      else 
         printline ( 'disabled' || '</TD></TR>');
      end if;
    END LOOP;


    printline ('</TABLE>');
  END LOOP; --end for(227)
end print_rules_actions;

procedure print_approval_groups is
  appr_grp_id_det       approval_group_id_table;
  appr_grp_query_det    attr_value_table;
  appr_grp_static_det   is_static_table;
  appr_grp_name_det     approval_group_name_table;
  appr_grp_item_id_det  approval_group_id_table;
  appr_par_nam_det      approval_group_par_name_table;
  appr_par_det          approval_group_par_table;
  appr_order_number     approval_group_id_table;
  v_approval_group_name varchar2(50);
begin
 --approvers details
 OPEN c_approval_groups;
 FETCH c_approval_groups BULK COLLECT INTO appr_grp_id_det, appr_grp_name_det,
            appr_grp_query_det,appr_grp_static_det;
 CLOSE c_approval_groups;
  
 printline ('<a name="Approval_Groups"></a><br>');        
 printline ('<a href="#top">Top</a>');
 printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
 printline('<TR bgcolor="#000099"><TD colspan="4" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Approval Groups</FONT></B></TD></TR>');
 FOR idx in 1..appr_grp_id_det.COUNT  LOOP
    printline('<TR bordercolor=blue><TD colspan="4" align="left">'||'<B>Group Id:</B>'||appr_grp_id_det (idx)||'<BR>');
    printline('<B>Name:</B>'||appr_grp_name_det (idx)||'<BR>');
    printline('<B>Static:</B>'||appr_grp_static_det (idx)||'<BR>');
    if appr_grp_static_det (idx) = 'N' then
      printline('<B>Query String:</B>'||appr_grp_query_det (idx)||'<BR>');    
      printline('</TD></TR>');
    else
        OPEN c_approval_group_items (appr_grp_id_det (idx));
        FETCH c_approval_group_items BULK COLLECT INTO
              appr_grp_item_id_det ,appr_par_nam_det,
              appr_par_det,appr_order_number;
        CLOSE c_approval_group_items;

        if appr_grp_item_id_det.COUNT <> 0 then
          printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
          printline('<TR BGCOLOR="#cccc66"><TD><B>Order Number</B></TD><TD><B>Item Id</B></TD><TD><B>Parameter Type</B></TD><TD><B>Parameter Value</B></TD></TR>');
          FOR idy in 1..appr_grp_item_id_det.COUNT LOOP
             printline('<TR><TD>');
             printline(appr_order_number (idy));
             printline('</TD><TD>');
             printline(appr_grp_item_id_det (idy));
             printline('</TD><TD>');
             printline(appr_par_nam_det (idy));
             printline('</TD><TD>');
             if appr_par_nam_det (idy) = 'OAM_group_id' then
               select name into v_approval_group_name
               from ame_approval_groups 
               where approval_group_id = appr_par_det (idy) 
                     and sysdate between start_date and 
                         nvl(end_date - (1/86400),sysdate);
               printline(v_approval_group_name);
             else
                 printline(appr_par_det (idy));
             end if;
             printline('</TD></TR>');
          END LOOP;
          printline('</TABLE>');
        else
            printline('<B>Members:</B> None');
        end if;
        printline('</TD></TR>');
    end if;
 END LOOP;
 printline ('</TABLE>');  
end print_approval_groups;

procedure print_configurations is
  config_name_det       config_vars_name_table;
  config_value_det      config_vars_value_table;
begin
  --configuration variables for application
  printline ('<a name="Config_Variables"></a><br>');        
  printline ('<a href="#top">Top</a>');
  printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
  printline('<TR bgcolor="#000099"><TD colspan="2" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Configuration Variables</FONT></B></TD></TR>');
  printline('<TR bgcolor="#00F2FA"><TD colspan="2" align="left"><B><FONT COLOR="#000099" FACE="arial,helvetica">Transaction Type Level</FONT></B></TD></TR>');
  OPEN c_config_vars (appId);
  FETCH c_config_vars BULK COLLECT INTO config_name_det,config_value_det;
  CLOSE c_config_vars;
  printline('<TR bgcolor="#cccc66"><TD>Variable Name</TD><TD>Variable Value</TD></TR>');
  FOR idx in 1..config_name_det.COUNT LOOP
     printline('<TR><TD>');
	  printline(config_name_det (idx));
	  printline('</TD><TD>');
	  printline(config_value_det (idx));
	  printline('</TD></TR>');
  END LOOP;

  printline('<TR bgcolor="#00F2FA"><TD colspan="2" align="left"><B><FONT COLOR="#000099" FACE="arial,helvetica">Default</FONT></B></TD></TR>');  
  printline('<TR bgcolor="#cccc66"><TD>Variable Name</TD><TD>Variable Value</TD></TR>');
  OPEN c_config_default_vars;
  FETCH c_config_default_vars BULK COLLECT INTO config_name_det,config_value_det;
  CLOSE c_config_default_vars;
  FOR idx in 1..config_name_det.COUNT LOOP
     printline('<TR><TD>');
     printline(config_name_det (idx));
     printline('</TD><TD>');
     printline(config_value_det (idx));
     printline('</TD></TR>');
  END LOOP;
  printline ('</TABLE>');
end print_configurations;


procedure print_action_types is
  action_details        action_id_table;
  action_desc_details   action_desc_table;
  action_type_details   action_type_name_table;
  action_param_details  action_parameter_table;
  act_typ_id_details    action_type_id_table;
  attr_name_details     attr_name_table;
  attr_type_details     attr_type_table;
begin
--action_types
  OPEN c_action_types;
  FETCH c_action_types BULK COLLECT INTO act_typ_id_details, action_type_details;
  CLOSE c_action_types;

  IF act_typ_id_details.COUNT > 0 THEN
     printline ('<BR><TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD colspan="3" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Action Types</FONT></B></TD></TR></table>');
     printline ('<a name="Action_Types"></a>');        
     printline ('<a href="#top">Top</a><br>');
   --give links to different action types
     printline ('<a href ="#Action_Types">Action Types</a>');
	  printline ('<ul>');
     FOR act_typ_idx in 1..act_typ_id_details.count loop
		  printline ('<li><a href = "#actiontype'||act_typ_id_details(act_typ_idx)||'">'||action_type_details(act_typ_idx)||'</a>');
	  end loop;
	  printline ('</ul>');
     FOR act_typ_idx IN 1 .. act_typ_id_details.COUNT LOOP
	     printline ('<BR><a href="#Action_Types">Back to Action Types</a>');
	     printline ('<a name="actiontype'||act_typ_id_details (act_typ_idx) ||'"></a>');
        printline ('<TABLE width="100%" BORDER ="1" cellspacing=0>');
        printline ('<BR><TR BGCOLOR="#00F2FA"><TD width=27%><B>Action Type ID   :' || act_typ_id_details (act_typ_idx) || '</B></TD>');
        printline ('<TD colspan="2"><B>Action Type Description : ' || action_type_details (act_typ_idx) || '</B></TD>');
        printline ('</TR>');

        OPEN c_action_type_attr_details (act_typ_id_details (act_typ_idx));
        FETCH c_action_type_attr_details BULK COLLECT INTO attr_name_details,  attr_type_details;
        CLOSE c_action_type_attr_details;

        IF attr_name_details.COUNT > 0 THEN
          printline ('<TR>');
          printline ('<TD BGCOLOR="#cccc66" colspan="3"><B>' || 'Required Attributes   :</B></TD>');
          printline ('</TR>');
          FOR attr_idx IN 1 .. attr_name_details.COUNT LOOP
             printline ('<TR>');
             printline ('<TD>' || attr_name_details (attr_idx) || '</TD>');
             printline ('<TD colspan="2">' || attr_type_details (attr_idx) || '</TD>');
             printline ('</TR>');
          END LOOP;
        END IF;

        OPEN c_action_type_action_details (act_typ_id_details (act_typ_idx));
        FETCH c_action_type_action_details BULK COLLECT INTO action_details, action_desc_details,action_param_details;
        CLOSE c_action_type_action_details;

        printline ('<TR BGCOLOR="#cccc66" >');
        printline ('<TD ><B>' || 'Action Id</B></TD>');
        printline('<TD><B>Action Description</B></TD>');
        printline('<TD><B>Parameter</B></TD>');
        printline ('</TR>');
        IF action_details.COUNT > 0 THEN
          FOR act_idx IN 1 .. action_details.COUNT LOOP
             printline ('<TR>');
             printline ('<TD>' || action_details (act_idx) || '</TD>');
             printline ('<TD>' || action_desc_details (act_idx) || '</TD>');
             printline('<TD>' || action_param_details (act_idx) || '</TD>');
             printline ('</TR>');
          END LOOP;
        ELSE
            printline ('<TR>');
            printline ('<TD colspan="2" align="center">[none]</TD>');
            printline ('</TR>');
        END IF;
        printline ('</TABLE>');
     END LOOP;
  END IF;
end print_action_types;

--main part of code
BEGIN
  printline ('<!--');
  appId := to_number(&1);
  printline ('-->');

  SELECT ameapp.application_name, fndapp.application_name,ameapp.application_id, ameapp.line_item_id_query,ameapp.transaction_type_id
    INTO txname, prodname, appid, line_item_query, tx_typ_id
    FROM ame_calling_apps ameapp, fnd_application_vl fndapp
    WHERE ameapp.application_id = appId
    AND fndapp.application_id = ameapp.fnd_application_id
    AND SYSDATE BETWEEN ameapp.start_date AND NVL((ameapp.end_date-(1/86400)),SYSDATE);

  printline ('<HTML>');
  printline ('<head>');
  printline ('<TITLE>AME Report</TITLE>');
  printline ('<style type="text/css">');
  printline ('a');
  printline ('{');
  printline ('color: blue;');
  printline ('text-decoration: none;');
  printline ('background: transparent;');
  printline ('}');
  printline ('a:hover');
  printline ('{');
  printline ('background: #ccddcc;');
  printline ('text-decoration: none;');
  printline ('color: #662222;');
  printline ('}');
  printline ('</style>');
  printline ('</head>');
  
  printline ('<BODY font="Arial">');
  printline ('<p align=center><B><font size=+1>AME Setup Details for Transaction Type : ' || txname || '</font></B></p>');
  printline ('<TABLE width="100%" BORDER ="1" cellspacing=0 BGCOLOR="#339999"><TR>');
  printline ('<TD><B>Product Name    : </B></TD><TD>' || prodname || '</TD></TR>');
  printline ('<TR><TD><B>Transaction Type ID  : </B></TD><TD>' || tx_typ_id || '</TD></TR>');
  if line_item_query is not null then
     printline ('<TR><TD><B>Line Item Query  : </B></TD><TD>');
     printline ( TRIM (line_item_query) ||'</TD><BR></TR>');
  end if;
  printline ('</TABLE><BR>');
  --links
  printline ('<a name="top"></a>');
  printline ('<a href="#Rule_Details1"><B>Rule Details</B></a><br>');
  printline ('<ul>');
  printline ('<li><a href="#Rule_Details1"><B>List Creation Rules</B></a>');
  printline ('<li><a href="#Rule_Details2"><B>List Creation Exception Rules</B></a>');
  printline ('<li><a href="#Rule_Details3"><B>List Modification Rules</B></a>');
  printline ('<li><a href="#Rule_Details4"><B>Substitution Rules</B></a>');
  printline ('<li><a href="#Rule_Details5"><B>Pre List Rules</B></a>');
  printline ('<li><a href="#Rule_Details6"><B>Post List Rules</B></a>');
  printline ('</ul>');
  printline ('<a href="#Mandatory"><B>Mandatory Attributes</B></a><br>');
  printline ('<a href="#Header"><B>Header Attributes</B></a><br>');
  if line_item_query is not null then
     printline ('<a href="#Line"><B>Line Item Attributes</B></a><br>');
  end if;
  printline ('<a href="#Action_Types"><B>Action Types</B></a><br>');
  printline ('<a href="#Approval_Groups"><B>Approval Groups</B></a><br>');
  printline ('<a href="#Config_Variables"><B>Configuration Variables</B></a><br>');
  --end of links code

--print rules and associated conditions and actions
print_rules_actions();

--mandatory_item
  OPEN c_attr_details_mandatory_item (appid);
  FETCH c_attr_details_mandatory_item BULK COLLECT 
   INTO attr_name_details,attr_type_details,attr_values,is_static_details;
  CLOSE c_attr_details_mandatory_item;
  print_attributes('Mandatory',attr_name_details,attr_type_details,is_static_details,attr_values);


--header_item
  OPEN c_attr_details_header_item (appid);
  FETCH c_attr_details_header_item BULK COLLECT INTO attr_name_details,attr_type_details,attr_values,
          is_static_details;
  CLOSE c_attr_details_header_item;
  print_attributes('Header',attr_name_details,attr_type_details,is_static_details,attr_values);

--line_item
  OPEN c_attr_details_line_item (appid);
  FETCH c_attr_details_line_item BULK COLLECT INTO attr_name_details,attr_type_details,attr_values,
          is_static_details;
  CLOSE c_attr_details_line_item;
  print_attributes('Line',attr_name_details,attr_type_details,is_static_details,attr_values);

--print action_types along with mandatory attributes and action ids of each action type
  print_action_types();
--print approval groups and their members
  print_approval_groups();
--print application and default configuration variables
  print_configurations();

  printline ('</BODY>');
  printline ('</HTML>');

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    printline ('No Data Found for the input.');
END;
/

SPOOL off;
EXIT;

